package com.dxl.lanju.dao;

import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.dxl.common.utils.SqlBuilder;
import com.dxl.dao.common.BaseDao;
import com.lanju.bean.ShopStatBean;
import com.lanju.bean.ShopStatByShopBean;
import com.lanju.bean.ShopStatByShopDayDetailBean;
import com.lanju.bean.ShopVisitByAreaDayBean;
import com.lanju.bean.ShopVisitByShopDayBean;

/**
 * 报表DAO
 * @author 邓小林
 */
@Repository
public class ReportDao extends BaseDao {
	private static final Logger LOG = Logger.getLogger(ReportDao.class);
	
	public List<ShopStatBean> shopStat() {
		SqlBuilder sb = new SqlBuilder("select round(sum(a.number * a.price), 2) as amount, d.name as areaName, c.id as id, c.name as shopName, c.type as shopType, c.tel as contact from `order_detail` a, `order` b, `shop` c, `area` d where a.order_id = b.id and b.shop_id = c.id and c.area_id = d.id group by c.id order by sum(a.number * a.price) desc");
		return super.list(ShopStatBean.class, sb.toStatement(), sb.toParams());
	}
	
	public List<ShopStatByShopBean> shopStat(String shopId) {
		SqlBuilder sb = new SqlBuilder("select round(sum(a.number * a.price), 2) as amount, DATE_FORMAT(a.created_time, '%Y-%m-%d') as day from `order_detail` a, `order` b where a.order_id = b.id");
		sb.appendStatement(" and b.shop_id = ").appendParamValue(shopId);
		sb.appendStatement(" group by DATE_FORMAT(a.created_time, '%Y-%m-%d') order by DATE_FORMAT(a.created_time, '%Y-%m-%d') desc");
		return super.list(ShopStatByShopBean.class, sb.toStatement(), sb.toParams());
	}
	
	public List<ShopStatByShopDayDetailBean> shopStat(String shopId, String day) {
		SqlBuilder sb = new SqlBuilder("select a.prod_name as prodName, CONCAT(d.color, '|', d.taste) as prodSku, a.number as sales, d.unit, a.price, round(a.number * a.price, 2) as amount from `order_detail` a, `order` b, `product` c, `product_sku` d where a.order_id = b.id and a.prod_id = c.id and a.sku_id = d.id and c.id = d.prod_id");
		sb.appendStatement(" and b.shop_id = ").appendParamValue(shopId);
		sb.appendStatement(" and DATE_FORMAT(a.created_time, '%Y-%m-%d') = ").appendParamValue(day);
		sb.appendStatement(" order by a.number desc");
		return super.list(ShopStatByShopDayDetailBean.class, sb.toStatement(), sb.toParams());
	}
	
	public List<Map<String, Object>> visitDayList(String areaId) {
		SqlBuilder sb = new SqlBuilder("select DATE_FORMAT(a.created_time, '%Y-%m-%d') as id, DATE_FORMAT(a.created_time, '%Y-%m-%d') as day from `visit` a, `shop` b, `area` c where a.shop_id = b.id and b.area_id = c.id");
		sb.appendStatement(" and c.id = ").appendParamValue(areaId);
		sb.appendStatement(" group by DATE_FORMAT(a.created_time, '%Y-%m-%d') order by DATE_FORMAT(a.created_time, '%Y-%m-%d') desc");
		return super.list(sb.toStatement(), sb.toParams());
	}
	
	public List<ShopVisitByAreaDayBean> shopVisit(String areaId, String day) {
		SqlBuilder sb = new SqlBuilder("select c.id, round(sum(a.number * a.price), 2) as amount, c.name as shopName, c.tel as contact, c.type as shopType, e.path from `order_detail` a, `order` b, `shop` c, `area` d, `visit` e where a.order_id = b.id and b.shop_id = c.id and c.area_id = d.id and c.id = e.shop_id and c.user_id = e.user_id");
		sb.appendStatement(" and d.id = ").appendParamValue(areaId);
		sb.appendStatement(" and DATE_FORMAT(e.created_time, '%Y-%m-%d') = ").appendParamValue(day);
		sb.appendStatement(" group by c.id order by a.number desc");
		return super.list(ShopVisitByAreaDayBean.class, sb.toStatement(), sb.toParams());
	}
	
	public List<ShopVisitByShopDayBean> shopVisit(String areaId, String shopId, String day) {
		SqlBuilder sb = new SqlBuilder("select a.prod_name as prodName, CONCAT(d.color, '|', d.taste) as prodSku, a.number as sales, d.unit, a.price, round(a.number * a.price, 2) as amount from `order_detail` a, `order` b, `product` c, `product_sku` d, `visit` e where a.order_id = b.id and a.prod_id = c.id and a.sku_id = d.id and c.id = d.prod_id and b.shop_id = e.shop_id and b.busi_id = e.user_id");
		sb.appendStatement(" and b.shop_id = ").appendParamValue(shopId);
		sb.appendStatement(" and DATE_FORMAT(a.created_time, '%Y-%m-%d') = ").appendParamValue(day);
		sb.appendStatement(" and DATE_FORMAT(e.created_time, '%Y-%m-%d') = ").appendParamValue(day);
		sb.appendStatement(" order by a.number desc");
		return super.list(ShopVisitByShopDayBean.class, sb.toStatement(), sb.toParams());
	}
}
